package jsyf.oa.dao.contract;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import jsyf.oa.dao.system.LogsDao;
import jsyf.oa.entity.contract.ContractMainEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by Administrator on 2018/6/20 0020.
 */
@SuppressWarnings("all")
@Repository
public class ContractMainDao extends BaseDao {

    @Autowired
    LogsDao logsDao;

    public ContractMainDao() {
        super.iBaseEntity = new ContractMainEntity();
    }

    public List<ContractMainEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<ContractMainEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            ContractMainEntity entity = (ContractMainEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, ContractMainEntity.class);
    }

    public List<JSONObject> selectByUserId(String userId, String condition, String companyIds, String userIds, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_con_main.ID AS id,\n" +
                "\tt_con_main.CONTRACT_NO AS contractNo,\n" +
                "\tt_con_main.CREATE_USER_ID AS createUserId,\n" +
                "\tt_con_main.CREATE_TIME AS createTime,\n" +
                "\tt_con_main.CITY AS city,\n" +
                "\tt_con_main.BRAND_NAME AS brandName,\n" +
                "\tt_sys_company.NAME AS seller,\n" +
                "\tt_con_main.SELLER_TAX_NO AS sellerTaxNo,\n" +
                "\tt_con_main.SELLER_ADDRESS AS sellerAddress,\n" +
                "\tt_con_main.SELLER_TEL AS sellerTel,\n" +
                "\tt_con_main.BUYER AS buyer,\n" +
                "\tt_con_main.MANUFACTURER AS manufacturer,\n" +
                "\tt_con_main.QUALITY_COMPANY AS qualityCompany,\n" +
                "\tt_con_main.FINANCE_COMPANY AS financeCompany,\n" +
                "\tt_con_main.BUYER_TAX_NO AS buyerTaxNo,\n" +
                "\tt_con_main.BUYER_ADDRESS AS buyerAddress,\n" +
                "\tt_con_main.BUYER_TEL AS buyerTel,\n" +
                "\tt_con_main.MODEL_NAME AS modelName,\n" +
                "\tt_con_main.DEVICE_TYPE AS deviceType,\n" +
                "\tt_con_main.SERIAL_NO AS serialNo,\n" +
                "\tt_con_main.BUY_TYPE AS buyType,\n" +
                "\tt_con_main.AUDIT_FLAG AS auditFlag,\n" +
                "\tt_con_main.CURRENT_AUDITOR_ID AS currentAuditorId,\n" +
                "\tt_con_main.CURRENT_AUDITOR_NAME AS currentAuditorName,\n" +
                "\tt_con_main.CAR_FLAG AS carFlag,\n" +
                "\tt_con_main.SELLER_COMPANY_ID AS sellerCompanyId,\n" +
                "\tt_con_main.AUTHENTICBRANDS_GROUP AS authenticbrandsGroup,\n" +
                "\tt_con_main.HAVE_REPAYMENT AS haveRepayment,\n" +
                "\tt_con_main.HAVE_REPLACEMENT AS haveReplacement,\n" +
                "\tt_con_main.HAVE_ADVANCES AS haveAdvances,\n" +
                "\tt_con_main.HAVE_UPLOAD_CONTRACT AS haveUploadContract,\n" +
                "\tt_con_main.SALES_PRICE AS salesPrice,\n" +
                "\tt_con_main.CREATE_USER_NAME AS createUserName,\n" +
                "\tt_con_main.DK_FIRST_MONEY AS dkFirstMoney,\n" +
                "\tt_con_main.REGION_MANAGER_NAME AS regionManagerName,\n" +
                "\tt_con_main.REGION_MANAGER_ID AS regionManagerId,\n" +
                "\tt_con_main.DK_SUM_TOTAL AS dkSumTotal,\n" +
                "\tt_con_main.DK_SUM_PRINCIPAL AS dkSumPrincipal,\n" +
                "\tt_con_main.DK_SUM_INTEREST AS dkSumInterest,\n" +
                "\tt_con_main.FQ_SUM_TOTAL AS fqSumTotal,\n" +
                "\tt_con_main.FQ_SUM_PRINCIPAL AS fqSumPrincipal,\n" +
                "\tt_con_main.FQ_SUM_INTEREST AS fqSumInterest,\n" +
                "\tt_con_main.PROVINCE AS province,\n" +
                "\tt_con_main.BRAND_ID AS brandId,\n" +
                "\tt_con_main.MODEL_ID AS modelId,\n" +
                "\tt_con_main.DK_FIRST_MONEYDX AS dkFirstMoneydx,\n" +
                "\tt_con_main.HAVE_DELIVERY AS haveDelivery,\n" +
                "\tt_con_main.DELIVERY_DATE AS deliveryDate,\n" +
                "\tt_con_main.REGION_DIRECTOR_ID AS regionDirectorId,\n" +
                "\tt_con_main.REGION_DIRECTOR_NAME AS regionDirectorName,\n" +
                "\tt_con_main.REGION_ID AS regionId,\n" +
                "\tt_con_main.REGION_NAME AS regionName,\n" +
                "\tt_con_main.LEGAL_REPRESENTATIVE_NAME AS legalRepresentativeName,\n" +
                "\tt_con_main.LEGAL_REPRESENTATIVE_TEL AS legalRepresentativeTel,\n" +
                "\tt_con_main.ACTUAL_CAR_BUYER AS actualCarBuyer,\n" +
                "\tt_con_main.ACTUAL_CAR_BUYER_TEL AS actualCarBuyerTel,\n" +
                "\tt_con_main.DELIVERY_ADDRESS AS deliveryAddress,\n" +
                "\tt_con_main.BIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                "\tt_con_main.BIG_REGION_MANAGER_NAME AS bigRegionManagerName,\n" +
                "\tt_con_main.CONTRACT_TYPE AS contractType,\n" +
                "\tt_con_main.BUYER_ID AS buyerId,\n" +
                "\tt_con_main.HAVE_OVERDUE AS haveOverdue,\n" +
                "\tt_con_main.CREDIT_MANAGER_NAME AS creditManagerName,\n" +
                "\tt_con_main.CREDIT_MANAGER_ID AS creditManagerId,\n" +
                "\tt_con_main.DATA_OFFICER_ID AS dataOfficerId,\n" +
                "\tt_con_main.DATA_OFFICER_NAME AS dataOfficerName,\n" +
                "\tt_con_main.PAYMENT_AMOUNT AS paymentAmount,\n" +
                "\tt_con_main.HAVE_SETTLE AS haveSettle,\n" +
                "\tt_con_main.PAYMENT_TIME AS paymentTime,\n" +
                "\tt_con_main.HAVE_ARRIVAL AS haveArrival,\n" +
                "\tt_con_main.TOTAL_DOWN_PAYMENT AS totalDownPayment,\n" +
                "\tt_con_main.TOTAL_ARREARS AS totalArrears,\n" +
                "\tt_con_main.TOTAL_CUSTOMER_ARREARS AS totalCustomerArrears,\n" +
                "\tt_con_main.ACCUMULATED_REPAYMENT_AMOUNT AS accumulatedRepaymentAmount,\n" +
                "\tt_con_main.REGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                "\tt_con_main.REGIONAL_SUPPORT_NAME AS regionalSupportName,\n" +
                "\tt_con_main.BUYER_TYPE AS buyerType,\n" +
                "\tt_dvc_outbound.TYPE AS outBoundType,\n" +
                "\tCASE t_con_main.AUDIT_FLAG\n" +
                "WHEN '0' THEN 1\n" +
                "WHEN '-1' THEN 2\n" +
                "WHEN '1' THEN 3\n" +
                "WHEN '2' THEN 4\n" +
                "ELSE 5\n" +
                "END AS sortNo\n" +
                "FROM t_con_main LEFT JOIN t_sys_copier ON t_con_main.ID=t_sys_copier.main_id\n" +
                "LEFT JOIN t_sys_audit ON t_sys_audit.MAIN_ID=t_con_main.ID\n" +
                "LEFT JOIN t_dvc_outbound ON t_con_main.ID = t_dvc_outbound.CONTRACT_ID \n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID = t_con_main.SELLER_COMPANY_ID \n" +
                "WHERE 1 = 1\n" +
                "AND  t_sys_company.DEL_FLAG= 0\n" +
                "AND ( t_con_main.CREATE_USER_ID ='" + userId + "'\n" +
                "OR (t_sys_copier.COPIER_ID='" + userId + "'  AND t_con_main.AUDIT_FLAG!='-1')\n" +
                "OR (t_sys_audit.AUDITOR_ID='" + userId + "' AND t_con_main.AUDIT_FLAG!='-1')\n" +
                "OR (t_con_main.REGION_MANAGER_ID='" + userId + "' AND t_con_main.AUDIT_FLAG!= '-1')\n" +
                "OR (t_con_main.REGION_MANAGER_ID IN (" + userIds + ") AND t_con_main.AUDIT_FLAG!= '-1')\n" +
                "OR (t_con_main.SELLER_COMPANY_ID IN (" + companyIds + ") AND t_con_main.AUDIT_FLAG!= '-1'))");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition + "\n");
        }
        sql.append("group by t_con_main.id\n" +
                "ORDER BY t_con_main.AUDIT_FLAG ASC,sortNo,t_con_main.CREATE_TIME desc");
        super.appendPageSql(sql, requestPage, pageSize);
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getListBymodelName(String condition, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_con_main.ID AS id,\n" +
                "\tCONTRACT_NO AS contractNo,\n" +
                "\tCREATE_USER_ID AS createUserId,\n" +
                "\tCREATE_TIME AS createTime,\n" +
                "\tCITY AS city,\n" +
                "\tBRAND_NAME AS brandName,\n" +
                "\tSELLER AS seller,\n" +
                "\tSELLER_TAX_NO AS sellerTaxNo,\n" +
                "\tSELLER_ADDRESS AS sellerAddress,\n" +
                "\tSELLER_TEL AS sellerTel,\n" +
                "\tBUYER AS buyer,\n" +
                "\tMANUFACTURER AS manufacturer,\n" +
                "\tQUALITY_COMPANY AS qualityCompany,\n" +
                "\tFINANCE_COMPANY AS financeCompany,\n" +
                "\tBUYER_TAX_NO AS buyerTaxNo,\n" +
                "\tBUYER_ADDRESS AS buyerAddress,\n" +
                "\tBUYER_TEL AS buyerTel,\n" +
                "\tMODEL_NAME AS modelName,\n" +
                "\tDEVICE_TYPE AS deviceType,\n" +
                "\tSERIAL_NO AS serialNo,\n" +
                "\tBUY_TYPE AS buyType,\n" +
                "\tt_con_main.AUDIT_FLAG AS auditFlag,\n" +
                "\tCURRENT_AUDITOR_ID AS currentAuditorId,\n" +
                "\tCURRENT_AUDITOR_NAME AS currentAuditorName,\n" +
                "\tCAR_FLAG AS carFlag,\n" +
                "\tSELLER_COMPANY_ID AS sellerCompanyId,\n" +
                "\tAUTHENTICBRANDS_GROUP AS authenticbrandsGroup,\n" +
                "\tHAVE_REPAYMENT AS haveRepayment,\n" +
                "\tHAVE_REPLACEMENT AS haveReplacement,\n" +
                "\tHAVE_ADVANCES AS haveAdvances,\n" +
                "\tHAVE_UPLOAD_CONTRACT AS haveUploadContract,\n" +
                "\tSALES_PRICE AS salesPrice,\n" +
                "\tCREATE_USER_NAME AS createUserName,\n" +
                "\tDK_FIRST_MONEY AS dkFirstMoney,\n" +
                "\tREGION_MANAGER_NAME AS regionManagerName,\n" +
                "\tREGION_MANAGER_ID AS regionManagerId,\n" +
                "\tDK_SUM_TOTAL AS dkSumTotal,\n" +
                "\tDK_SUM_PRINCIPAL AS dkSumPrincipal,\n" +
                "\tDK_SUM_INTEREST AS dkSumInterest,\n" +
                "\tFQ_SUM_TOTAL AS fqSumTotal,\n" +
                "\tFQ_SUM_PRINCIPAL AS fqSumPrincipal,\n" +
                "\tFQ_SUM_INTEREST AS fqSumInterest,\n" +
                "\tPROVINCE AS province,\n" +
                "\tBRAND_ID AS brandId,\n" +
                "\tMODEL_ID AS modelId,\n" +
                "\tDK_FIRST_MONEYDX AS dkFirstMoneydx,\n" +
                "\tHAVE_DELIVERY AS haveDelivery,\n" +
                "\tDELIVERY_DATE AS deliveryDate,\n" +
                "\tREGION_DIRECTOR_ID AS regionDirectorId,\n" +
                "\tREGION_DIRECTOR_NAME AS regionDirectorName,\n" +
                "\tREGION_ID AS regionId,\n" +
                "\tREGION_NAME AS regionName,\n" +
                "\tLEGAL_REPRESENTATIVE_NAME AS legalRepresentativeName,\n" +
                "\tLEGAL_REPRESENTATIVE_TEL AS legalRepresentativeTel,\n" +
                "\tACTUAL_CAR_BUYER AS actualCarBuyer,\n" +
                "\tACTUAL_CAR_BUYER_TEL AS actualCarBuyerTel,\n" +
                "\tDELIVERY_ADDRESS AS deliveryAddress,\n" +
                "\tBIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                "\tBIG_REGION_MANAGER_NAME AS bigRegionManagerName,\n" +
                "\tCONTRACT_TYPE AS contractType,\n" +
                "\tBUYER_ID AS buyerId,\n" +
                "\tHAVE_OVERDUE AS haveOverdue,\n" +
                "\tCREDIT_MANAGER_NAME AS creditManagerName,\n" +
                "\tCREDIT_MANAGER_ID AS creditManagerId,\n" +
                "\tDATA_OFFICER_ID AS dataOfficerId,\n" +
                "\tDATA_OFFICER_NAME AS dataOfficerName,\n" +
                "\tPAYMENT_AMOUNT AS paymentAmount,\n" +
                "\tHAVE_SETTLE AS haveSettle,\n" +
                "\tPAYMENT_TIME AS paymentTime,\n" +
                "\tHAVE_ARRIVAL AS haveArrival,\n" +
                "\tTOTAL_DOWN_PAYMENT AS totalDownPayment,\n" +
                "\tTOTAL_ARREARS AS totalArrears,\n" +
                "\tTOTAL_CUSTOMER_ARREARS AS totalCustomerArrears,\n" +
                "\tACCUMULATED_REPAYMENT_AMOUNT AS accumulatedRepaymentAmount,\n" +
                "\tREGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                "\tREGIONAL_SUPPORT_NAME AS regionalSupportName,\n" +
                "\tBUYER_TYPE AS buyerType " +
                "FROM t_con_main " +
                "WHERE 1 = 1\n");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition + "\n");
        }
        sql.append("group by t_con_main.id\n" +
                "ORDER BY t_con_main.CREATE_TIME desc");
        super.appendPageSql(sql, requestPage, pageSize);
        return super.queryForJsonList(sql.toString());
    }

    public String getCompanyFGetpy(String companyName) {
        String sql = "SELECT  F_GETPY ('" + companyName + "')  as fGetpy\n";
        return super.queryForString(sql);
    }

    public List<JSONObject> getRegionNameByUserId(String userId) {
        String sql = "Select replace(t_sys_region.`NAME`,'大区','') as regionName,\n" +
                "t_sys_region.ID as regionId\n" +
                "FROM t_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE t_sys_user_2_region.USER_ID='" + userId + "'\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                "AND t_sys_region.PARENT_ID=0";
        return super.queryForJsonList(sql);
    }

    public List<JSONObject> getRegionManagerByUserId(String conditon, Object[] array) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "v_sys_part_time_for_report.userId as id,\n" +
                "v_sys_part_time_for_report.userName as name,\n" +
                "v_sys_part_time_for_report.jobState as jobState,\n" +
                "t_sys_company.`NAME` as companyName,\n" +
                "t_sys_depart.`NAME` as departName,\n" +
                "t_sys_role_name.`NAME` as roleName\n" +
                "FROM v_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=v_sys_part_time_for_report.companyId\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=v_sys_part_time_for_report.departId\n" +
                "WHERE v_sys_part_time_for_report.roleNameId\n" +
                "AND t_sys_depart.DEL_FLAG =0\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_role_name.FRONT_FLAG =1\n");
        sql.append(conditon);
        return super.queryForJsonList(sql.toString(), array);
    }

    public JSONObject getContractAccountSubDate(String mainId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tdq.contractRepayMentAmount,\n" +
                "\tdq.date,\n" +
                "\tdq.mainId,\n" +
                "\tdq.repayMent,\n" +
                "\tdq.total,\n" +
                "\tdq.type,\n" +
                "\tqkfx.overDuePenalty,\n" +
                "\tqkfx.remainingArrears\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tdate1.REPAYMENT_DATE AS repayMent,\n" +
                "\t\t\tdate1.TOTAL AS total,\n" +
                "\t\t\tdate1.CONTRACT_REPAYMENT_AMOUNT AS contractRepayMentAmount,\n" +
                "\t\t\tdate1.TYPE AS type,\n" +
                "\t\t\tdate1.date,\n" +
                "\t\t\tdate1.MAIN_ID AS mainId\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_con_repayment.*, ABS(\n" +
                "\t\t\t\t\t\tdatediff(\n" +
                "\t\t\t\t\t\t\tCURDATE(),\n" +
                "\t\t\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t) AS date\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_con_repayment\n" +
                "\t\t\t\tLEFT JOIN t_con_main ON t_con_repayment.MAIN_ID = t_con_main.ID\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_con_repayment.PARENT_ID = '0'\n" +
                "\t\t\t\tAND t_con_main.ID = '"+mainId+"'\n" +
                "\t\t\t\tORDER BY\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE,\n" +
                "\t\t\t\t\tt_con_repayment.TOTAL DESC,\n" +
                "\t\t\t\t\tt_con_repayment.CONTRACT_REPAYMENT_AMOUNT DESC\n" +
                "\t\t\t) AS date1\n" +
                "\t\tORDER BY\n" +
                "\t\t\tdate1.date,\n" +
                "\t\t\tdate1.TOTAL DESC,\n" +
                "\t\t\tdate1.CONTRACT_REPAYMENT_AMOUNT DESC\n" +
                "\t\tLIMIT 0,\n" +
                "\t\t1\n" +
                "\t) dq\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tSUM(a.overDuePenalty) AS overDuePenalty,\n" +
                "\t\tSUM(a.remainingArrears) AS remainingArrears,\n" +
                "\t\ta.mainId\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_con_repayment.OVERDUE_PENALTY AS overDuePenalty,\n" +
                "\t\t\t\tt_con_repayment.REMAINING_ARREARS AS remainingArrears,\n" +
                "\t\t\t\tt_con_repayment.MAIN_ID AS mainId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_con_repayment\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tt_con_repayment.MAIN_ID = '"+mainId+"'\n" +
                "\t\t\tUNION ALL\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_con_financingment.JR_OVERDUE_PENALTY AS overDuePenalty,\n" +
                "\t\t\t\t\tt_con_financingment.REMAINING_ARREARS AS remainingArrears,\n" +
                "\t\t\t\t\tt_con_financingment.MAIN_ID AS mainId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_con_financingment\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tt_con_financingment.MAIN_ID = '"+mainId+"'\n" +
                "\t\t) AS a\n" +
                ") AS qkfx ON dq.mainId = qkfx.mainId");
        return super.queryForJsonObject(sql.toString());
    }

    public List<JSONObject> getContractAccountType(String mainId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "t_con_repayment.TYPE as type\n" +
                "   FROM t_con_repayment LEFT JOIN t_con_main ON t_con_repayment.MAIN_ID=t_con_main.ID\n" +
                "WHERE 1=1\n" +
                "AND t_con_main.ID='" + mainId + "' \n" +
                "\n" +
                "GROUP BY t_con_repayment.TYPE\n" +
                "ORDER BY t_con_repayment.REPAYMENT_DATE");
        return super.queryForJsonList(sql.toString());
    }


    public List<JSONObject> selectContractAccountList(String condition, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_con_main.ID AS id,\n" +
                "\tt_con_main.CONTRACT_NO AS contractNo,\n" +
                "\tt_con_main.CREATE_USER_ID AS createUserId,\n" +
                "\tt_con_main.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_con_main.HAVE_OVERDUE_NO AS haveOverdueNo,\n" +
                "\tt_con_main.CREATE_TIME AS createTime,\n" +
                "\tt_con_main.CITY AS city,\n" +
                "\tt_con_main.BRAND_NAME AS brandName,\n" +
                "\tt_SYS_COMPANY.NAME AS seller,\n" +
                "\tt_con_main.SELLER_TAX_NO AS sellerTaxNo,\n" +
                "\tt_con_main.SELLER_ADDRESS AS sellerAddress,\n" +
                "\tt_con_main.SELLER_TEL AS sellerTel,\n" +
                "\tt_con_main.BUYER AS buyer,\n" +
                "\tt_con_main.EXEMPT AS exempt,\n" +
                " t_con_main.MANUFACTURER AS manufacturer,\n" +
                " t_con_main.QUALITY_COMPANY AS qualityCompany,\n" +
                " t_con_main.FINANCE_COMPANY AS financeCompany,\n" +
                " t_con_main.BUYER_TAX_NO AS buyerTaxNo,\n" +
                " t_con_main.INTEREST_FREE_AUDIT_FLAG AS interestFreeAuditFlag,\n" +
                " t_con_main.INTEREST_FREE_AUDIT_NAME AS interestFreeAuditName,\n" +
                " t_con_main.INTEREST_FREE_AUDIT_ID AS interestFreeAuditId,\n" +
                " t_con_main.APPLY_NAME AS applyName,\n" +
                " t_con_main.APPLY_DEPART AS applyDepart,\n" +
                " t_con_main.APPLY_NAME_ID AS applyNameId,\n" +
                " t_con_main.BUYER_ADDRESS AS buyerAddress,\n" +
                " t_con_main.BUYER_TEL AS buyerTel,\n" +
                " t_con_main.MODEL_NAME AS modelName,\n" +
                " t_con_main.DEVICE_TYPE AS deviceType,\n" +
                " t_con_main.SERIAL_NO AS serialNo,\n" +
                " t_con_main.BUY_TYPE AS buyType,\n" +
                " t_con_main.AUDIT_FLAG AS auditFlag,\n" +
                " t_con_main.CURRENT_AUDITOR_ID AS currentAuditorId,\n" +
                " t_con_main.CURRENT_AUDITOR_NAME AS currentAuditorName,\n" +
                " t_con_main.CAR_FLAG AS carFlag,\n" +
                " t_con_main.SELLER_COMPANY_ID AS sellerCompanyId,\n" +
                " t_con_main.AUTHENTICBRANDS_GROUP AS authenticbrandsGroup,\n" +
                " t_con_main.HAVE_REPAYMENT AS haveRepayment,\n" +
                " t_con_main.TAKE_OVER_MANAGER AS takeOverManager,\n" +
                " t_con_main.HAVE_REPLACEMENT AS haveReplacement,\n" +
                " t_con_main.HAVE_ADVANCES AS haveAdvances,\n" +
                " t_con_main.HAVE_UPLOAD_CONTRACT AS haveUploadContract,\n" +
                " t_con_main.SALES_PRICE AS salesPrice,\n" +
                " t_con_main.CREATE_USER_NAME AS createUserName,\n" +
                " t_con_main.DK_FIRST_MONEY AS dkFirstMoney,\n" +
                " t_con_main.REGION_MANAGER_NAME AS regionManagerName,\n" +
                " t_con_main.REGION_MANAGER_ID AS regionManagerId,\n" +
                " t_con_main.DK_SUM_TOTAL AS dkSumTotal,\n" +
                " t_con_main.DK_SUM_PRINCIPAL AS dkSumPrincipal,\n" +
                " t_con_main.DK_SUM_INTEREST AS dkSumInterest,\n" +
                " t_con_main.FQ_SUM_TOTAL AS fqSumTotal,\n" +
                " t_con_main.FQ_SUM_PRINCIPAL AS fqSumPrincipal,\n" +
                " t_con_main.FQ_SUM_INTEREST AS fqSumInterest,\n" +
                " t_con_main.PROVINCE AS province,\n" +
                " t_con_main.BRAND_ID AS brandId,\n" +
                " t_con_main.MODEL_ID AS modelId,\n" +
                " t_con_main.DK_FIRST_MONEYDX AS dkFirstMoneydx,\n" +
                " t_con_main.HAVE_DELIVERY AS haveDelivery,\n" +
                " t_con_main.DELIVERY_DATE AS deliveryDate,\n" +
                " t_con_main.REGION_DIRECTOR_ID AS regionDirectorId,\n" +
                " t_con_main.REGION_DIRECTOR_NAME AS regionDirectorName,\n" +
                " t_con_main.REGION_ID AS regionId,\n" +
                " t_con_main.REGION_NAME AS regionName,\n" +
                " t_con_main.LEGAL_REPRESENTATIVE_NAME AS legalRepresentativeName,\n" +
                " t_con_main.LEGAL_REPRESENTATIVE_TEL AS legalRepresentativeTel,\n" +
                " t_con_main.ACTUAL_CAR_BUYER AS actualCarBuyer,\n" +
                " t_con_main.ACTUAL_CAR_BUYER_TEL AS actualCarBuyerTel,\n" +
                " t_con_main.DELIVERY_ADDRESS AS deliveryAddress,\n" +
                " t_con_main.BIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                " t_con_main.BIG_REGION_MANAGER_NAME AS bigRegionManagerName,\n" +
                " t_con_main.CONTRACT_TYPE AS contractType,\n" +
                " t_con_main.BUYER_ID AS buyerId,\n" +
                " t_con_main.HAVE_OVERDUE AS haveOverdue,\n" +
                " t_con_main.CREDIT_MANAGER_NAME AS creditManagerName,\n" +
                " t_con_main.CREDIT_MANAGER_ID AS creditManagerId,\n" +
                " t_con_main.DATA_OFFICER_ID AS dataOfficerId,\n" +
                " t_con_main.DATA_OFFICER_NAME AS dataOfficerName,\n" +
                " t_con_main.PAYMENT_AMOUNT AS paymentAmount,\n" +
                " t_con_main.HAVE_SETTLE AS haveSettle,\n" +
                " t_con_main.PAYMENT_TIME AS paymentTime,\n" +
                " t_con_main.DEVICE_STATE AS deviceState,\n" +
                " t_con_main.HAVE_ARRIVAL AS haveArrival,\n" +
                " t_con_main.TOTAL_DOWN_PAYMENT AS totalDownPayment,\n" +
                " t_con_main.TOTAL_ARREARS AS totalArrears,\n" +
                " t_con_main.SITE_POSITION AS sitePosition,\n" +
                " t_con_main.ACTUAL_USER_TEL AS actualUserTel,\n" +
                " t_con_main.ACTUAL_USER AS actualUser,\n" +
                " t_con_main.TOTAL_CUSTOMER_ARREARS AS totalCustomerArrears,\n" +
                " t_con_main.ACCUMULATED_REPAYMENT_AMOUNT AS accumulatedRepaymentAmount,\n" +
                " t_con_main.REGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                " t_con_main.REGIONAL_SUPPORT_NAME AS regionalSupportName,\n" +
                " t_con_main.BUYER_TYPE AS buyerType,\n" +
                " t_con_sales.TOTAL AS total,\n" +
                " t_con_sales.MONTHLY_REPAYMENT AS monthlyRepayment,\n" +
                " t_con_sales.FINANCING_AMOUNT AS financingAmount,\n" +
                " t_con_sales.LOAN_TERM AS loanTerm,\n" +
                " t_con_sales.FIRST_PAYMENT AS firstPayment,\n" +
                " t_con_sales.UNIT_PRICE AS unitPrice,\n" +
                " CASE t_con_main.AUDIT_FLAG\n" +
                "WHEN '0' THEN\n" +
                "\t1\n" +
                "WHEN '-1' THEN\n" +
                "\t2\n" +
                "WHEN '1' THEN\n" +
                "\t3\n" +
                "WHEN '2' THEN\n" +
                "\t4\n" +
                "ELSE\n" +
                "\t5\n" +
                "END AS sortNo\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_SYS_COMPANY ON t_con_main.SELLER_COMPANY_ID = t_SYS_COMPANY.ID\n" +
                "LEFT JOIN t_con_sales ON t_con_main.ID = t_con_sales.MAIN_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_SYS_COMPANY.DEL_FLAG = 0\n" +
                "AND t_con_main.AUDIT_FLAG = 1\n" +
                "AND t_con_sales.LOG_FLAG = 0\n");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition + "\n");
        }
        sql.append("group by t_con_main.id\n" +
                "ORDER BY t_con_main.HAVE_OVERDUE_NO DESC,t_con_main.CREATE_TIME desc");
        super.appendPageSql(sql, requestPage, pageSize);
        return super.queryForJsonList(sql.toString());

    }
    public List<JSONObject> selectContractAccountListApp(String condition, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_con_main.BUYER AS buyer,\n" +
                "\tt_con_main.HAVE_DSDF AS haveDsdf,\n" +
                "\tt_SYS_COMPANY.`NAME` AS seller,\n" +
                "\tt_con_main.`HAVE_SETTLE` AS haveSettle,\n" +
                "\tt_con_main.ID AS id,\n" +
                "\tt_con_main.AUDIT_FLAG,\n" +
                "\trepayment.*\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "\tLEFT JOIN t_SYS_COMPANY ON t_con_main.SELLER_COMPANY_ID = t_SYS_COMPANY.ID\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\ta.*\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE AS repaymentDate,\n" +
                "\t\t\t\t\tCASE\n" +
                "\t\t\t\tWHEN TYPE = 0 THEN\n" +
                "\t\t\t\t\tTOTAL\n" +
                "\t\t\t\tWHEN TYPE = 1 THEN\n" +
                "\t\t\t\t\tTOTAL\n" +
                "\t\t\t\tWHEN TYPE = 2 THEN\n" +
                "\t\t\t\t\tCONTRACT_REPAYMENT_AMOUNT\n" +
                "\t\t\t\tWHEN TYPE = 3 THEN\n" +
                "\t\t\t\t\tTOTAL\n" +
                "\t\t\t\tWHEN TYPE = 4 THEN\n" +
                "\t\t\t\t\tCONTRACT_REPAYMENT_AMOUNT\n" +
                "\t\t\t\tEND AS total,\n" +
                "\t\t\t\tt_con_repayment.REMAINING_ARREARS AS remainingArrears,\n" +
                "\t\t\t\tt_con_repayment.OVERDUE_PENALTY AS overduePenalty,\n" +
                "\t\t\t\tt_con_repayment.TYPE AS type,\n" +
                "\t\t\t\tt_con_repayment.MAIN_ID AS mainId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_con_repayment\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t(\n" +
                "\t\t\t\t\tOVERDUE_PENALTY != ''\n" +
                "\t\t\t\t\tAND OVERDUE_PENALTY != 0.00\n" +
                "\t\t\t\t\tAND OVERDUE_PENALTY IS NOT NULL\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tOR (\n" +
                "\t\t\t\t\tREMAINING_ARREARS != ''\n" +
                "\t\t\t\t\tAND REMAINING_ARREARS != 0.00\n" +
                "\t\t\t\t\tAND REMAINING_ARREARS IS NOT NULL\n" +
                "\t\t\t\t)\n"+
                "\t\t\t\tOR (\n" +
                "\t\t\t\t\tto_days(\n" +
                "\t\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t\t) - to_days(now()) <= 5\n" +
                "\t\t\t\t\tAND to_days(\n" +
                "\t\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t\t) - to_days(now()) >= 0\n" +
                "\tAND PARENT_ID='0'\n" +
                "\t\t\t\t)\n" +
                "\t\t\t)\n" +
                "\t\t\tORDER BY\n" +
                "\t\t\t\tREPAYMENT_DATE\n" +
                "\t\t\t)\n" +
                "\t\t\tUNION ALL\n" +
                "\t\t\t\t(\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tt_con_financingment.FINANCING_DATE AS repaymentDate,\n" +
                "\t\t\t\t\t\tt_con_financingment.FINANCING_MONEY AS total,\n" +
                "\t\t\t\t\t\tt_con_financingment.REMAINING_ARREARS AS remainingArrears,\n" +
                "\t\t\t\t\t\tt_con_financingment.JR_OVERDUE_PENALTY AS overduePenalty,\n" +
                "\t\t\t\t\t\tt_con_financingment.TYPE + 8 AS type,\n" +
                "\t\t\t\t\t\tt_con_financingment.MAIN_ID AS mainId\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tt_con_financingment\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t1 = 1\n" +
                "\t\t\t\t\tAND (\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\tJR_OVERDUE_PENALTY != ''\n" +
                "\t\t\t\t\t\t\tAND jr_OVERDUE_PENALTY IS NOT NULL\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\tOR (\n" +
                "\t\t\t\t\tREMAINING_ARREARS != ''\n" +
                "\t\t\t\t\tAND REMAINING_ARREARS != 0.00\n" +
                "\t\t\t\t\tAND REMAINING_ARREARS IS NOT NULL\n" +
                "\t\t\t\t)\n"+
                "\t\t\t\t\t\tOR (\n" +
                "\t\t\t\t\t\t\tto_days(\n" +
                "\t\t\t\t\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t\t\t\t\t) - to_days(now()) <= 5\n" +
                "\t\t\t\t\t\t\tAND to_days(\n" +
                "\t\t\t\t\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t\t\t\t\t) - to_days(now()) >= 0\n" +
                "\tAND PARENT_ID='0'\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\t\tORDER BY\n" +
                "\t\t\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t\t)\n" +
                "\t\t) AS a\n" +
                "\tORDER BY\n" +
                "\t\ta.repaymentDate DESC\n" +
                ") AS repayment ON t_con_main.ID = repayment.mainId\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "\tAND t_SYS_COMPANY.DEL_FLAG = 0\n"+
                "AND t_con_main.AUDIT_FLAG = 1\n");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition + "\n");
        }
        sql.append(" ORDER BY t_con_main.HAVE_OVERDUE_NO DESC,t_con_main.CREATE_TIME desc,repayment.repaymentDate ");
        super.appendPageSql(sql, requestPage, pageSize);
        return super.queryForJsonList(sql.toString());

    }

    public List<JSONObject> getAllContractAccount(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_con_main.ID AS id,\n" +
                "\tt_con_main.CONTRACT_NO AS contractNo,\n" +
                "\tt_con_main.CREATE_USER_ID AS createUserId,\n" +
                "\tt_con_main.CREATE_TIME AS createTime,\n" +
                "\tt_con_main.CITY AS city,\n" +
                "\tt_con_main.BRAND_NAME AS brandName,\n" +
                "\tt_SYS_COMPANY.NAME AS seller,\n" +
                "\tt_con_main.SELLER_TAX_NO AS sellerTaxNo,\n" +
                "\tt_con_main.SELLER_ADDRESS AS sellerAddress,\n" +
                "\tt_con_main.SELLER_TEL AS sellerTel,\n" +
                "\tt_con_main.BUYER AS buyer,\n" +
                "\tt_con_main.MANUFACTURER AS manufacturer,\n" +
                "\tt_con_main.QUALITY_COMPANY AS qualityCompany,\n" +
                "\tt_con_main.FINANCE_COMPANY AS financeCompany,\n" +
                "\tt_con_main.BUYER_TAX_NO AS buyerTaxNo,\n" +
                "\tt_con_main.BUYER_ADDRESS AS buyerAddress,\n" +
                "\tt_con_main.BUYER_TEL AS buyerTel,\n" +
                "\tt_con_main.MODEL_NAME AS modelName,\n" +
                "\tt_con_main.DEVICE_TYPE AS deviceType,\n" +
                "\tt_con_main.SERIAL_NO AS serialNo,\n" +
                "\tt_con_main.BUY_TYPE AS buyType,\n" +
                "\tt_con_main.AUDIT_FLAG AS auditFlag,\n" +
                "\tt_con_main.CURRENT_AUDITOR_ID AS currentAuditorId,\n" +
                "\tt_con_main.CURRENT_AUDITOR_NAME AS currentAuditorName,\n" +
                "\tt_con_main.CAR_FLAG AS carFlag,\n" +
                "\tt_con_main.SELLER_COMPANY_ID AS sellerCompanyId,\n" +
                "\tt_con_main.AUTHENTICBRANDS_GROUP AS authenticbrandsGroup,\n" +
                "\tt_con_main.HAVE_REPAYMENT AS haveRepayment,\n" +
                "\tt_con_main.HAVE_REPLACEMENT AS haveReplacement,\n" +
                "\tt_con_main.HAVE_ADVANCES AS haveAdvances,\n" +
                "\tt_con_main.HAVE_UPLOAD_CONTRACT AS haveUploadContract,\n" +
                "CASE t_con_main.SALES_PRICE\n" +
                "WHEN t_con_main.BUY_TYPE='全款' THEN t_con_sales.TOTAL_PRICE \n" +
                "WHEN t_con_main.BUY_TYPE='内部分期' THEN t_con_sales.TOTAL_PRICE \n" +
                "ELSE t_con_sales.UNIT_PRICE \n" +
                "END AS salesPrice,\n" +
                "\tt_con_main.CREATE_USER_NAME AS createUserName,\n" +
                "\tt_con_main.DK_FIRST_MONEY AS dkFirstMoney,\n" +
                "\tt_con_main.REGION_MANAGER_NAME AS regionManagerName,\n" +
                "\tt_con_main.REGION_MANAGER_ID AS regionManagerId,\n" +
                "\tt_con_main.DK_SUM_TOTAL AS dkSumTotal,\n" +
                "\tt_con_main.DK_SUM_PRINCIPAL AS dkSumPrincipal,\n" +
                "\tt_con_main.DK_SUM_INTEREST AS dkSumInterest,\n" +
                "\tt_con_main.FQ_SUM_TOTAL AS fqSumTotal,\n" +
                "\tt_con_main.FQ_SUM_PRINCIPAL AS fqSumPrincipal,\n" +
                "\tt_con_main.FQ_SUM_INTEREST AS fqSumInterest,\n" +
                "\tt_con_main.PROVINCE AS province,\n" +
                "\tt_con_main.BRAND_ID AS brandId,\n" +
                "\tt_con_main.MODEL_ID AS modelId,\n" +
                "\tt_con_main.DK_FIRST_MONEYDX AS dkFirstMoneydx,\n" +
                "\tt_con_main.HAVE_DELIVERY AS haveDelivery,\n" +
                "\tt_con_main.DELIVERY_DATE AS deliveryDate,\n" +
                "\tt_con_main.REGION_DIRECTOR_ID AS regionDirectorId,\n" +
                "\tt_con_main.REGION_DIRECTOR_NAME AS regionDirectorName,\n" +
                "\tt_con_main.REGION_ID AS regionId,\n" +
                "\tt_con_main.REGION_NAME AS regionName,\n" +
                "\tt_con_main.LEGAL_REPRESENTATIVE_NAME AS legalRepresentativeName,\n" +
                "\tt_con_main.LEGAL_REPRESENTATIVE_TEL AS legalRepresentativeTel,\n" +
                "\tt_con_main.ACTUAL_CAR_BUYER AS actualCarBuyer,\n" +
                "\tt_con_main.ACTUAL_CAR_BUYER_TEL AS actualCarBuyerTel,\n" +
                "\tt_con_main.DELIVERY_ADDRESS AS deliveryAddress,\n" +
                "\tt_con_main.BIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                "\tt_con_main.BIG_REGION_MANAGER_NAME AS bigRegionManagerName,\n" +
                "\tt_con_main.CONTRACT_TYPE AS contractType,\n" +
                "\tt_con_main.BUYER_ID AS buyerId,\n" +
                "\tt_con_main.HAVE_OVERDUE AS haveOverdue,\n" +
                "\tt_con_main.CREDIT_MANAGER_NAME AS creditManagerName,\n" +
                "\tt_con_main.CREDIT_MANAGER_ID AS creditManagerId,\n" +
                "\tt_con_main.DATA_OFFICER_ID AS dataOfficerId,\n" +
                "\tt_con_main.DATA_OFFICER_NAME AS dataOfficerName,\n" +
                "\tt_con_main.PAYMENT_AMOUNT AS paymentAmount,\n" +
                "\tt_con_main.HAVE_SETTLE AS haveSettle,\n" +
                "\tt_con_main.PAYMENT_TIME AS paymentTime,\n" +
                "\tt_con_main.HAVE_ARRIVAL AS haveArrival,\n" +
                "\tt_con_main.TOTAL_DOWN_PAYMENT AS totalDownPayment,\n" +
                "\tt_con_main.TOTAL_ARREARS AS totalArrears,\n" +
                "\tt_con_main.TOTAL_CUSTOMER_ARREARS AS totalCustomerArrears,\n" +
                "\tt_con_main.ACCUMULATED_REPAYMENT_AMOUNT AS accumulatedRepaymentAmount,\n" +
                "\tt_con_main.REGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                "\tt_con_main.REGIONAL_SUPPORT_NAME AS regionalSupportName,\n" +
                "\tt_con_main.BUYER_TYPE AS buyerType,\n" +
                "\tCASE t_con_main.AUDIT_FLAG\n" +
                "WHEN '0' THEN 1\n" +
                "WHEN '-1' THEN 2\n" +
                "WHEN '1' THEN 3\n" +
                "WHEN '2' THEN 4\n" +
                "ELSE 5\n" +
                "END AS sortNo,\n" +
                "t_con_sales.TOTAL_PRICE AS totalPrice,\n" +
                "t_con_sales.UNIT_PRICE AS unitPrice,\n" +
                "t_con_sales.PRICE_OFFER AS priceOffer,\n" +
                "t_con_sales.STAGE_TERM AS stageTerm,\n" +
                "t_con_sales.FIRST_PAYMENT AS firstPayment,\n" +
                "t_con_sales.STAGE_TERM AS stageTerm,\n" +
                "t_con_sales.LOAN_TERM AS loanTerm,\n" +
                "t_con_sales.DOWN_PAYMENT AS downPayment,\n" +
                "t_con_sales.FIRST_PAYMENT AS firstPayment,\n" +
                "t_con_sales.BOND AS bond,\n" +
                "t_con_sales.SERVICE_CHARGE AS serviceCharge,\n" +
                "t_con_sales.SECURITY_CHARGE AS securityCharge,\n" +
                "t_con_sales.INSURANCE_PREMIUM AS insurancePremium,\n" +
                "t_con_sales.GPS_CHARGE AS gpsCharge,\n" +
                "t_con_replacement.REPLACEMENTS_NAME AS replacementsName,\n" +
                "\n" +
                "t_con_guarantor.GUARANTOR AS guarantor,\n" +
                "t_con_guarantor.GUARANTOR_TAX_NO AS guarantorTaxNo,\n" +
                "t_con_guarantor.GUARANTOR_TEL AS guarantorTel,\n" +
                "t_con_guarantor.GUARANTOR_ADDRESS AS guarantorAddress,\n" +
                "\n" +
                "t_dvc_displace.BRAND_NAME AS jbrandName,\n" +
                "t_dvc_displace.REPLACEMENT_TYPE AS jreplacementType,\n" +
                "t_dvc_displace.MODEL_NAME AS jmodelName,\n" +
                "t_dvc_displace.SEQUENCE AS jsequence,\n" +
                "t_dvc_displace.ESTIMATE_PRICE AS jestimatePrice,\n" +
                "t_dvc_displace.REPLACEMENT_PRICE AS jreplacementPrice,\n" +
                "t_dvc_displace.SELL_PRICE AS jsellPrice,\n" +
                "t_dvc_displace.REPLACEMENT_SUBSIDY AS jreplacementSubsidy, \n" +
                "\n" +
                "t_dvc_displace_room.REPLACEMENT_TYPE AS replacementType,\n" +
                "t_dvc_displace_room.STORE_ADDRESS AS storeAddress,\n" +
                "t_dvc_displace_room.HOUSE_NAME AS houseName,\n" +
                "t_dvc_displace_room.BUILDING_NUMBER AS buildingNumber,\n" +
                "t_dvc_displace_room.HOUSE_ADDRESS AS houseAddress,\n" +
                "t_dvc_displace_room.AREA AS area,\n" +
                "t_dvc_displace_room.HOUSES_PROPERTY AS housesProperty,\n" +
                "t_dvc_displace_room.STATE AS state,\n" +
                "t_dvc_displace_room.TRANSFER AS transfer,\n" +
                "t_dvc_displace_room.ESTIMATE_PRICE AS estimatePrice,\n" +
                "t_dvc_displace_room.REPLACEMENT_PRICE AS replacementPrice,\n" +
                "t_dvc_displace_room.SELL_PRICE AS sellPrice,\n" +
                "t_dvc_displace_room.REMARK AS remark\n" +
                "FROM t_con_main \n" +
                "LEFT JOIN t_SYS_COMPANY ON t_con_main.SELLER_COMPANY_ID=t_SYS_COMPANY.ID \n" +
                "LEFT JOIN t_con_sales ON t_con_sales.MAIN_ID=t_con_main.ID\n" +
                "LEFT JOIN t_con_replacement ON t_con_replacement.MAIN_ID=t_con_main.ID \n" +
                "LEFT JOIN t_dvc_displace ON t_dvc_displace.REPLACEMENT_ID=t_con_replacement.ID AND t_dvc_displace.TYPE=1\n" +
                "LEFT JOIN t_dvc_displace AS t_dvc_displace_room ON t_dvc_displace_room.REPLACEMENT_ID=t_con_replacement.ID AND t_dvc_displace_room.TYPE=3\n" +
                "LEFT JOIN t_con_guarantor ON t_con_guarantor.MAIN_ID=t_con_main.ID\n" +
                "WHERE 1 = 1\n" +
                "AND  t_SYS_COMPANY.DEL_FLAG=0\n");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition + "\n");
        }
        sql.append("group by t_con_main.id\n" +
                "ORDER BY t_con_main.HAVE_OVERDUE DESC,t_con_main.HAVE_OVERDUE_NO DESC,t_con_main.CREATE_TIME desc");
        return super.queryForJsonList(sql.toString());

    }

    public List<JSONObject> senderExpire() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tt_con_main.ID AS mainId,\n" +
                "\tt_con_main.BUYER AS buyer,\n" +
                "\tto_days(now()) - to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) AS repaymentDate\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tto_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) <= 5\n" +
                "\tAND to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) >= 0\n" +
                ")\n" +
                "AND (\n" +
                "\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                ")\n" +
                "AND t_con_main.HAVE_OVERDUE != 1\n" +
                "AND t_con_main.HAVE_DSDF = '0'\n" +
                "AND t_con_main.BUY_TYPE='内部分期'\n" +
                "AND (\n" +
                "\tt_con_repayment.TYPE = '3'\n" +
                "\tOR t_con_repayment.TYPE = '1'\n" +
                "OR t_con_repayment.TYPE = '0'\n" +
                ")\n" +
                "UNION\n" +
                "SELECT\n" +
                "\tt_con_main.ID AS mainId,\n" +
                "\tt_con_main.BUYER AS buyer,\n" +
                "\tto_days(now()) - to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) AS repaymentDate\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tto_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) <= 5\n" +
                "\tAND to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) >= 0\n" +
                ")\n" +
                "AND (\n" +
                "\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                ")\n" +
                "AND t_con_main.HAVE_OVERDUE != 1\n" +
                "AND t_con_main.HAVE_DSDF = '0'\n" +
                "AND t_con_main.BUY_TYPE!='内部分期'\n" +
                "AND (\n" +
                "\tt_con_repayment.TYPE = '3'\n" +
                "\tOR t_con_repayment.TYPE = '1'\n" +
                ")\n" +
                "UNION\n" +
                "\tSELECT\n" +
                "\t\tt_con_main.ID AS mainId,\n" +
                "\t\tt_con_main.BUYER AS buyer,\n" +
                "\t\tto_days(now()) - to_days(\n" +
                "\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t) AS repaymentDate\n" +
                "\tFROM\n" +
                "\t\tt_con_main\n" +
                "\tLEFT JOIN t_con_financingment ON t_con_main.ID = t_con_financingment.MAIN_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND (\n" +
                "\t\tto_days(\n" +
                "\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t) - to_days(now()) <= 5\n" +
                "\t\tAND to_days(\n" +
                "\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t) - to_days(now()) >= 0\n" +
                "\t)\n" +
                "\tAND t_con_main.HAVE_DSDF = '0'\n" +
                "\tAND t_con_main.HAVE_OVERDUE != 1\n" +
                "AND t_con_main.BUY_TYPE!='内部分期'\n" +
                "\tUNION\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_con_main.ID AS mainId,\n" +
                "\t\t\tt_con_main.BUYER AS buyer,\n" +
                "\t\t\tto_days(now()) - to_days(\n" +
                "\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t) AS repaymentDate\n" +
                "\t\tFROM\n" +
                "\t\t\tt_con_main\n" +
                "\t\tLEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND (\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t) - to_days(now()) <= 5\n" +
                "\t\t\tAND to_days(\n" +
                "\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t) - to_days(now()) >= 0\n" +
                "\t\t)\n" +
                "\t\tAND (\n" +
                "\t\t\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\t\t\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                "\t\t)\n" +
                "\t\tAND t_con_main.HAVE_DSDF = '1'\n" +
                "\t\tAND t_con_main.HAVE_OVERDUE != 1");
        return queryForJsonList(sb.toString());
    }
    //获取快到期
    public List<JSONObject> getJSONListKdq() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_con_main.ID AS id,\n" +
                "\tt_con_main.BUYER AS buyer,\n" +
                "\tt_con_main.TAKE_OVER_MANAGER_ID AS takeOverManagerId,\n" +
                "\tto_days(now()) - to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) AS repaymentDate\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tto_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) <= 5\n" +
                "\tAND to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) >= 0\n" +
                ")\n" +
                "AND (\n" +
                "\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                ")\n" +
                "AND t_con_main.HAVE_DSDF = '0'\n" +
                "AND t_con_main.BUY_TYPE = '内部分期'\n" +
                "AND (\n" +
                "\tt_con_repayment.TYPE = '3'\n" +
                "\tOR t_con_repayment.TYPE = '1'\n" +
                "\tOR t_con_repayment.TYPE = '0'\n" +
                ")\n" +
                "UNION\n" +
                "\tSELECT\n" +
                "\t\tt_con_main.ID AS id,\n" +
                "\t\tt_con_main.BUYER AS buyer,\n" +
                "\t\tt_con_main.TAKE_OVER_MANAGER_ID AS takeOverManagerId,\n" +
                "\t\tto_days(now()) - to_days(\n" +
                "\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t) AS repaymentDate\n" +
                "\tFROM\n" +
                "\t\tt_con_main\n" +
                "\tLEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND (\n" +
                "\t\tto_days(\n" +
                "\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t) - to_days(now()) <= 5\n" +
                "\t\tAND to_days(\n" +
                "\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t) - to_days(now()) >= 0\n" +
                "\t)\n" +
                "\tAND (\n" +
                "\t\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\t\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                "\t)\n" +
                "\tAND t_con_main.HAVE_DSDF = '0'\n" +
                "\tAND t_con_main.BUY_TYPE != '内部分期'\n" +
                "\tAND (\n" +
                "\t\tt_con_repayment.TYPE = '3'\n" +
                "\t\tOR t_con_repayment.TYPE = '1'\n" +
                "\t)\n" +
                "\tUNION\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_con_main.ID AS id,\n" +
                "\t\t\tt_con_main.BUYER AS buyer,\n" +
                "t_con_main.TAKE_OVER_MANAGER_ID AS takeOverManagerId,\n" +
                "\t\t\tto_days(now()) - to_days(\n" +
                "\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t) AS repaymentDate\n" +
                "\t\tFROM\n" +
                "\t\t\tt_con_main\n" +
                "\t\tLEFT JOIN t_con_financingment ON t_con_main.ID = t_con_financingment.MAIN_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND (\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t) - to_days(now()) <= 5\n" +
                "\t\t\tAND to_days(\n" +
                "\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t) - to_days(now()) >= 0\n" +
                "\t\t)\n" +
                "\t\tAND t_con_main.HAVE_DSDF = '0'\n" +
                "\t\tAND t_con_main.BUY_TYPE != '内部分期'\n" +
                "\t\tUNION\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_con_main.ID AS id,\n" +
                "\t\t\t\tt_con_main.BUYER AS buyer,\n" +
                "\t\t\t\tt_con_main.TAKE_OVER_MANAGER_ID AS takeOverManagerId,\n" +
                "\t\t\t\tto_days(now()) - to_days(\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t) AS repaymentDate\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_con_main\n" +
                "\t\t\tLEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tto_days(\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t) - to_days(now()) <= 5\n" +
                "\t\t\t\tAND to_days(\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t) - to_days(now()) >= 0\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\t\t\t\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t_con_main.HAVE_DSDF = '1'");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());

        return jsonList;
    }
    public List<JSONObject> senderExpireList() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "t_con_repayment.MAIN_ID AS mainId \n"+
                "FROM\n" +
                "\tt_con_repayment\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tto_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) > 0\n" +
                "\tAND to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) < 5\n" +
                ")\n" +
                "AND t_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "GROUP BY t_con_repayment.MAIN_ID");
        return queryForJsonList(sb.toString());
    }
    public List<JSONObject> selectExportAccountList() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_con_main.ID AS id,\n" +
                "\t\t\tt_con_main.CONTRACT_NO AS contractNo,\n" +
                "\t\t\tt_con_main.CREATE_USER_ID AS createUserId,\n" +
                "\t\t\tt_con_main.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\t\t\tt_con_main.HAVE_OVERDUE_NO AS haveOverdueNo,\n" +
                "\t\t\tt_con_main.CREATE_TIME AS createTime,\n" +
                "\t\t\tt_con_main.CITY AS city,\n" +
                "\t\t\tt_con_main.BRAND_NAME AS brandName,\n" +
                "\t\t\tt_SYS_COMPANY. NAME AS seller,\n" +
                "\t\t\tt_con_main.SELLER_TAX_NO AS sellerTaxNo,\n" +
                "\t\t\tt_con_main.SELLER_ADDRESS AS sellerAddress,\n" +
                "\t\t\tt_con_main.SELLER_TEL AS sellerTel,\n" +
                "\t\t\tt_con_main.BUYER AS buyer,\n" +
                "\t\t\tt_con_main.MANUFACTURER AS manufacturer,\n" +
                "\t\t\tt_con_main.QUALITY_COMPANY AS qualityCompany,\n" +
                "\t\t\tt_con_main.FINANCE_COMPANY AS financeCompany,\n" +
                "\t\t\tt_con_main.BUYER_TAX_NO AS buyerTaxNo,\n" +
                "\t\t\tt_con_main.BUYER_ADDRESS AS buyerAddress,\n" +
                "\t\t\tt_con_main.BUYER_TEL AS buyerTel,\n" +
                "\t\t\tt_con_main.MODEL_NAME AS modelName,\n" +
                "\t\t\tt_con_main.DEVICE_TYPE AS deviceType,\n" +
                "\t\t\tt_con_main.SERIAL_NO AS serialNo,\n" +
                "\t\t\tt_con_main.BUY_TYPE AS buyType,\n" +
                "\t\t\tt_con_main.AUDIT_FLAG AS auditFlag,\n" +
                "\t\t\tt_con_main.CURRENT_AUDITOR_ID AS currentAuditorId,\n" +
                "\t\t\tt_con_main.CURRENT_AUDITOR_NAME AS currentAuditorName,\n" +
                "\t\t\tt_con_main.CAR_FLAG AS carFlag,\n" +
                "\t\t\tt_con_main.SELLER_COMPANY_ID AS sellerCompanyId,\n" +
                "\t\t\tt_con_main.AUTHENTICBRANDS_GROUP AS authenticbrandsGroup,\n" +
                "\t\t\tt_con_main.HAVE_REPAYMENT AS haveRepayment,\n" +
                "\t\t\tt_con_main.TAKE_OVER_MANAGER AS takeOverManager,\n" +
                "\t\t\tt_con_main.HAVE_REPLACEMENT AS haveReplacement,\n" +
                "\t\t\tt_con_main.HAVE_ADVANCES AS haveAdvances,\n" +
                "\t\t\tt_con_main.HAVE_UPLOAD_CONTRACT AS haveUploadContract,\n" +
                "\t\t\tt_con_main.SALES_PRICE AS salesPrice,\n" +
                "\t\t\tt_con_main.CREATE_USER_NAME AS createUserName,\n" +
                "\t\t\tt_con_main.DK_FIRST_MONEY AS dkFirstMoney,\n" +
                "\t\t\tt_con_main.REGION_MANAGER_NAME AS regionManagerName,\n" +
                "\t\t\tt_con_main.REGION_MANAGER_ID AS regionManagerId,\n" +
                "\t\t\tt_con_main.DK_SUM_TOTAL AS dkSumTotal,\n" +
                "\t\t\tt_con_main.DK_SUM_PRINCIPAL AS dkSumPrincipal,\n" +
                "\t\t\tt_con_main.DK_SUM_INTEREST AS dkSumInterest,\n" +
                "\t\t\tt_con_main.FQ_SUM_TOTAL AS fqSumTotal,\n" +
                "\t\t\tt_con_main.FQ_SUM_PRINCIPAL AS fqSumPrincipal,\n" +
                "\t\t\tt_con_main.FQ_SUM_INTEREST AS fqSumInterest,\n" +
                "\t\t\tt_con_main.PROVINCE AS province,\n" +
                "\t\t\tt_con_main.BRAND_ID AS brandId,\n" +
                "\t\t\tt_con_main.MODEL_ID AS modelId,\n" +
                "\t\t\tt_con_main.DK_FIRST_MONEYDX AS dkFirstMoneydx,\n" +
                "\t\t\tt_con_main.HAVE_DELIVERY AS haveDelivery,\n" +
                "\t\t\tt_con_main.DELIVERY_DATE AS deliveryDate,\n" +
                "\t\t\tt_con_main.REGION_DIRECTOR_ID AS regionDirectorId,\n" +
                "\t\t\tt_con_main.REGION_DIRECTOR_NAME AS regionDirectorName,\n" +
                "\t\t\tt_con_main.REGION_ID AS regionId,\n" +
                "\t\t\tt_con_main.REGION_NAME AS regionName,\n" +
                "\t\t\tt_con_main.LEGAL_REPRESENTATIVE_NAME AS legalRepresentativeName,\n" +
                "\t\t\tt_con_main.LEGAL_REPRESENTATIVE_TEL AS legalRepresentativeTel,\n" +
                "\t\t\tt_con_main.ACTUAL_CAR_BUYER AS actualCarBuyer,\n" +
                "\t\t\tt_con_main.ACTUAL_CAR_BUYER_TEL AS actualCarBuyerTel,\n" +
                "\t\t\tt_con_main.DELIVERY_ADDRESS AS deliveryAddress,\n" +
                "\t\t\tt_con_main.BIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                "\t\t\tt_con_main.BIG_REGION_MANAGER_NAME AS bigRegionManagerName,\n" +
                "\t\t\tt_con_main.CONTRACT_TYPE AS contractType,\n" +
                "\t\t\tt_con_main.BUYER_ID AS buyerId,\n" +
                "\t\t\tt_con_main.HAVE_OVERDUE AS haveOverdue,\n" +
                "\t\t\tt_con_main.CREDIT_MANAGER_NAME AS creditManagerName,\n" +
                "\t\t\tt_con_main.CREDIT_MANAGER_ID AS creditManagerId,\n" +
                "\t\t\tt_con_main.DATA_OFFICER_ID AS dataOfficerId,\n" +
                "\t\t\tt_con_main.DATA_OFFICER_NAME AS dataOfficerName,\n" +
                "\t\t\tt_con_main.PAYMENT_AMOUNT AS paymentAmount,\n" +
                "\t\t\tt_con_main.HAVE_SETTLE AS haveSettle,\n" +
                "\t\t\tt_con_main.PAYMENT_TIME AS paymentTime,\n" +
                "\t\t\tt_con_main.DEVICE_STATE AS deviceState,\n" +
                "\t\t\tt_con_main.HAVE_ARRIVAL AS haveArrival,\n" +
                "\t\t\tt_con_main.TOTAL_DOWN_PAYMENT AS totalDownPayment,\n" +
                "\t\t\tt_con_main.TOTAL_ARREARS AS totalArrears,\n" +
                "\t\t\tt_con_main.SITE_POSITION AS sitePosition,\n" +
                "\t\t\tt_con_main.ACTUAL_USER_TEL AS actualUserTel,\n" +
                "\t\t\tt_con_main.ACTUAL_USER AS actualUser,\n" +
                "\t\t\tt_con_main.TOTAL_CUSTOMER_ARREARS AS totalCustomerArrears,\n" +
                "\t\t\tt_con_main.ACCUMULATED_REPAYMENT_AMOUNT AS accumulatedRepaymentAmount,\n" +
                "\t\t\tt_con_main.REGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                "\t\t\tt_con_main.REGIONAL_SUPPORT_NAME AS regionalSupportName,\n" +
                "\t\t\tt_con_main.BUYER_TYPE AS buyerType,\n" +
                "\t\t\tt_con_sales.TOTAL AS total,\n" +
                "\t\t\tt_con_sales.MONTHLY_REPAYMENT AS monthlyRepayment,\n" +
                "\t\t\tt_con_sales.FINANCING_AMOUNT AS financingAmount,\n" +
                "\t\t\tt_con_sales.LOAN_TERM AS loanTerm,\n" +
                "\t\t\tt_con_sales.FIRST_PAYMENT AS firstPayment,\n" +
                "\t\t\tt_con_sales.UNIT_PRICE AS unitPrice,\n" +
                "\t\t\tt_con_collection.CREATE_USER_NAME AS collectionCreateUserName,\n" +
                "\t\t\tt_con_collection.COLLECTION_DATE AS collectionDate,\n" +
                "\t\t\tt_con_collection.COLLECTION AS collection,\n" +
                "\t\t\tCASE t_con_main.AUDIT_FLAG\n" +
                "\t\tWHEN '0' THEN\n" +
                "\t\t\t1\n" +
                "\t\tWHEN '-1' THEN\n" +
                "\t\t\t2\n" +
                "\t\tWHEN '1' THEN\n" +
                "\t\t\t3\n" +
                "\t\tWHEN '2' THEN\n" +
                "\t\t\t4\n" +
                "\t\tELSE\n" +
                "\t\t\t5\n" +
                "\t\tEND AS sortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_con_main\n" +
                "\t\tLEFT JOIN t_SYS_COMPANY ON t_con_main.SELLER_COMPANY_ID = t_SYS_COMPANY.ID\n" +
                "\t\tLEFT JOIN t_con_sales ON t_con_main.ID = t_con_sales.MAIN_ID\n" +
                "\t\tLEFT JOIN t_con_collection ON t_con_main.ID = t_con_collection.MAIN_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_SYS_COMPANY.DEL_FLAG = 0\n" +
                "\t\tAND t_con_main.AUDIT_FLAG = 1\n" +
                "\t\tAND t_con_main.HAVE_DELIVERY = '1'\n" +
                "\t\tAND t_con_sales.LOG_FLAG = 0\n" +
                "\t) AS main\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND main.collectionDate IN (\n" +
                "\tSELECT\n" +
                "\t\tMAX(\n" +
                "\t\t\tt_con_collection.COLLECTION_DATE\n" +
                "\t\t)\n" +
                "\tFROM\n" +
                "\t\tt_con_collection\n" +
                "\tWHERE\n" +
                "\t\tt_con_collection.MAIN_ID = main.id\n" +
                ")\n" +
                "OR main.collectionDate IS NULL\n" +
                "GROUP BY\n" +
                "\tmain.id\n" +
                "ORDER BY\n" +
                "\tmain.haveOverdue DESC,\n" +
                "\tmain.haveOverdueNo DESC,\n" +
                "\tmain.createTime DESC");
        return super.queryForJsonList(sql.toString());

    }
    public Integer selectContractAccountCount(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1)\n" +
                "FROM t_con_main \n" +
                "WHERE 1 = 1\n");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition + "\n");
        }
        String count = super.queryForString(sql.toString());
        return Integer.parseInt(count);
    }

    public List<JSONObject> selectReceivable() {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tSUM(t_con_sales.FIRST_PAYMENT+t_con_main.FQ_FIRST_MONEY) AS firstPayMent,\n" +
                "\tSUM(t_con_sales.FINANCING_AMOUNT) AS financingAmount\n" +
                "FROM\n" +
                "\tt_sys_company\n" +
                "LEFT JOIN t_con_main ON t_sys_company.ID = t_con_main.SELLER_COMPANY_ID\n" +
                "LEFT JOIN t_con_sales ON t_con_main.ID = t_con_sales.MAIN_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA'\n" +
                "AND t_sys_company.id != '2017121617484A2796D8F52B6B558B94'\n" +
                "AND t_sys_company.DEL_FLAG = 0\n" +
                "AND t_con_main.AUDIT_FLAG=1\n" +
                "AND t_con_main.HAVE_DELIVERY=1\n" +
                "GROUP BY\n" +
                "\tt_sys_company.ID\n" +
                "ORDER BY t_sys_company.SORT_NO");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSales0List(String dateTime) {
        String month = "";
        String dateMonth = dateTime.substring(0, 7);
        if (dateTime.substring(5, 7).startsWith("0")) {
            month = dateTime.substring(6, 7);
        } else {
            month = dateTime.substring(5, 7);
        }
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n" +
                "v_sys_company.* ,\n" +
                "IFNULL(newSample.newSampleCount,0) as newSampleCount,\n" +
                "IFNULL(oldSample.oldSampleCount,0) as oldSampleCount,\n" +
                "IFNULL(newContract.newDeliveryNum,0) as newDeliveryNum,\n" +
                "IFNULL(oldContract.oldDeliveryNum,0) as oldDeliveryNum,\n" +
                "SUM(CASE WHEN YEAR(newMonth.date)=YEAR(NOW())-1 AND MONTH(newMonth.date) ='11' THEN newMonth.newNum ELSE 0 END)  as new11,\n" +
                "SUM(CASE WHEN YEAR(newMonth.date)=YEAR(NOW())-1 AND MONTH(newMonth.date) ='12' THEN newMonth.newNum ELSE 0 END)  as new12,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='1' THEN newMonth.newNum ELSE 0 END)  as new1,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='2' THEN newMonth.newNum ELSE 0 END)  as new2,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='3' THEN newMonth.newNum ELSE 0 END)  as new3,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='4' THEN newMonth.newNum ELSE 0 END)  as new4,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='5' THEN newMonth.newNum ELSE 0 END)  as new5,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='6' THEN newMonth.newNum ELSE 0 END)  as new6,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='7' THEN newMonth.newNum ELSE 0 END)  as new7,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='8' THEN newMonth.newNum ELSE 0 END)  as new8,\n" +
                "SUM(CASE WHEN MONTH(newMonth.date) ='9' THEN newMonth.newNum ELSE 0 END)  as new9,\n" +
                "SUM(CASE WHEN YEAR(newMonth.date)=YEAR(NOW()) AND MONTH(newMonth.date) ='10' THEN newMonth.newNum ELSE 0 END)  as new10,\n" +
                "SUM(CASE WHEN YEAR(newMonth.date)=YEAR(NOW()) AND MONTH(newMonth.date) ='11' THEN newMonth.newNum ELSE 0 END)  as new13,\n" +
                "SUM(CASE WHEN YEAR(newMonth.date)=YEAR(NOW()) AND MONTH(newMonth.date) ='12' THEN newMonth.newNum ELSE 0 END)  as new14,\n" +
                "SUM(CASE WHEN YEAR(oldMonth.date)=YEAR(NOW())-1 AND MONTH(oldMonth.date) ='11' THEN oldMonth.oldNum ELSE 0 END)  as old11,\n" +
                "SUM(CASE WHEN YEAR(oldMonth.date)=YEAR(NOW())-1 AND MONTH(oldMonth.date) ='12' THEN oldMonth.oldNum ELSE 0 END)  as old12,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='1' THEN oldMonth.oldNum ELSE 0 END)  as old1,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='2' THEN oldMonth.oldNum ELSE 0 END)  as old2,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='3' THEN oldMonth.oldNum ELSE 0 END)  as old3,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='4' THEN oldMonth.oldNum ELSE 0 END)  as old4,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='5' THEN oldMonth.oldNum ELSE 0 END)  as old5,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='6' THEN oldMonth.oldNum ELSE 0 END)  as old6,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='7' THEN oldMonth.oldNum ELSE 0 END)  as old7,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='8' THEN oldMonth.oldNum ELSE 0 END)  as old8,\n" +
                "SUM(CASE WHEN MONTH(oldMonth.date) ='9' THEN oldMonth.oldNum ELSE 0 END)  as old9,\n" +
                "SUM(CASE WHEN YEAR(oldMonth.date)=YEAR(NOW()) AND MONTH(oldMonth.date) ='10' THEN oldMonth.oldNum ELSE 0 END)  as old10,\n" +
                "SUM(CASE WHEN YEAR(oldMonth.date)=YEAR(NOW()) AND MONTH(oldMonth.date) ='11' THEN oldMonth.oldNum ELSE 0 END)  as old13,\n" +
                "SUM(CASE WHEN YEAR(oldMonth.date)=YEAR(NOW()) AND MONTH(oldMonth.date) ='12' THEN oldMonth.oldNum ELSE 0 END)  as old14,\n" +
                "IFNULL(SUM(newMonth.newNum),0) as newTotal,\n" +
                "IFNULL(SUM(oldMonth.oldNum),0) as oldTotal\n" +
                "FROM\n" +
                "(SELECT\n" +
                "\tt_sys_company.ID AS companyId,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tt_sys_company.SORT_NO AS sortNo\n" +
                "FROM\n" +
                "\tt_sys_company\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA'\n" +
                "AND t_sys_company.DEL_FLAG = 0) as v_sys_company\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_dvc_sample.FK_COMPANY_ID AS companyId,\n" +
                "\t\tCOUNT(t_dvc_sample.ID) AS newSampleCount\n" +
                "\tFROM\n" +
                "\t\tt_dvc_sample\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\n" +
                "\tAND t_dvc_sample.STATE = '库存'\n" +
                "\tGROUP BY\n" +
                "\t\tt_dvc_sample.FK_COMPANY_ID\n" +
                ") AS newSample ON newSample.companyId = v_sys_company.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_dvc_displace.FK_COMPANY_ID AS companyId,\n" +
                "\t\tCOUNT(t_dvc_displace.ID) AS oldSampleCount\n" +
                "\tFROM\n" +
                "\t\tt_dvc_displace\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\n" +
                "\tAND t_dvc_displace.STATE IN ('库存', '未售')\n" +
                "\tGROUP BY\n" +
                "\t\tt_dvc_displace.FK_COMPANY_ID\n" +
                ") AS oldSample ON oldSample.companyId = v_sys_company.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_con_main.SELLER_COMPANY_ID AS companyId,\n" +
                "\t\tCOUNT(t_con_main.ID) AS newDeliveryNum\n" +
                "\tFROM\n" +
                "\t\tt_con_main\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_con_main.CONTRACT_TYPE = 1\n" +
                "\tAND t_con_main.DELIVERY_DATE LIKE '%" + dateTime + "%'\n" +
                "\tGROUP BY\n" +
                "\t\tt_con_main.SELLER_COMPANY_ID\n" +
                ") AS newContract ON newContract.companyId = v_sys_company.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_con_main.SELLER_COMPANY_ID AS companyId,\n" +
                "\t\tCOUNT(t_con_main.ID) AS oldDeliveryNum\n" +
                "\tFROM\n" +
                "\t\tt_con_main\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_con_main.CONTRACT_TYPE = 2\n" +
                "\tAND t_con_main.DELIVERY_DATE like '%" + dateTime + "%'\n" +
                "\tGROUP BY\n" +
                "\t\tt_con_main.SELLER_COMPANY_ID\n" +
                ") AS oldContract ON oldContract.companyId = v_sys_company.companyId\n" +
                "LEFT JOIN\t(\n" +
                "\t\t\tSELECT\n" +
                "        t_con_main.DELIVERY_DATE as date,\n" +
                "\t\t\t\tt_con_main.SELLER_COMPANY_ID AS companyId,\n" +
                "\t\t\t\tCOUNT(t_con_main.ID) AS newNum\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_con_main\n" +
                "\t\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_con_main.SELLER_COMPANY_ID\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_con_main.CONTRACT_TYPE = 1\n" +
                "\t\t\tAND t_con_main.DELIVERY_DATE IS NOT NULL\n" +
                "\t\t\tGROUP BY\n" +
                "\t\t\t\tt_con_main.DELIVERY_DATE,\n" +
                "\t\t\t\tt_con_main.SELLER_COMPANY_ID\n" +
                "\t\t) AS newMonth ON newMonth.companyId=v_sys_company.companyId\n" +
                "LEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "      t_con_main.DELIVERY_DATE as date,\n" +
                "\t\t\tt_con_main.SELLER_COMPANY_ID AS companyId,\n" +
                "\t\t\tCOUNT(t_con_main.ID) AS oldNum\n" +
                "\t\tFROM\n" +
                "\t\t\tt_con_main\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_con_main.SELLER_COMPANY_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_con_main.CONTRACT_TYPE = 2\n" +
                "\t\tAND t_con_main.DELIVERY_DATE IS NOT NULL\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt_con_main.DELIVERY_DATE,\n" +
                "\t\t\t\tt_con_main.SELLER_COMPANY_ID\n" +
                "\t) AS oldMonth ON v_sys_company.companyId = oldMonth.companyId\n" +
                "GROUP BY v_sys_company.sortNo ");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getContractBuyer(String condition,Object[] params) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t_con_main.BUYER as customerName,t_con_main.BUYER_TEL as customerTel\n" +
                "FROM t_con_main\n" );
                sb.append(  condition);
                sb.append(        "GROUP BY t_con_main.BUYER_TEL \n");
        return queryForJsonList(sb.toString(),params);
    }

    public Integer updateExpire() {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE `t_con_main` SET HAVE_OVERDUE=0,HAVE_OVERDUE_NO=-6 WHERE HAVE_OVERDUE!=1");
        return this.getJdbcTemplate().update(sb.toString());
    }

    public List<JSONObject> senderMessage() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tt_con_main.ID AS mainId,\n" +
                "\tt_con_main.BUYER AS customerName,\n" +
                "\tt_con_main.BUYER_TEL AS customerTel,\n" +
                "\tt_con_main.BUY_TYPE AS buyType,\n" +
                "\tt_con_main.BRAND_NAME AS brandName,\n" +
                "\tt_con_main.BUYER_TAX_NO AS buyerTaxNo,\n" +
                "\tt_con_main.MODEL_NAME AS modelName,\n" +
                "\tt_con_main.DEVICE_TYPE AS deviceName,\n" +
                "\tt_con_main.REGION_MANAGER_ID AS regionManagerId,\n" +
                "\tt_con_main.BIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                "\tt_con_main.REGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                "\tt_con_main.CREATE_USER_ID AS createUserId,\n" +
                "\tt_con_main.CREDIT_MANAGER_ID AS creditManagerId,\n" +
                "\tt_con_main.DATA_OFFICER_ID AS dataOfficerId,\n" +
                "\tt_con_main.TAKE_OVER_MANAGER_ID AS takeOverManagerId,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "t_con_repayment.REPAYMENT_DATE AS repaymentDate,\n" +
                "CASE\n" +
                "WHEN t_con_main.BRAND_NAME='约翰迪尔'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -6) \n" +
                "WHEN t_con_main.BRAND_NAME='利勃海尔'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -5)\n" +
                "WHEN t_con_main.BRAND_NAME='凯斯'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -4)\t\n" +
                "END AS serialNo\t\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "LEFT JOIN t_sys_company ON t_con_main.SELLER_COMPANY_ID =t_sys_company.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tto_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) >= 0\n" +
                "\tAND to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) < 5\n" +
                ") AND t_con_repayment.REPAYMENT_AMOUNT='0.00' ");
        return queryForJsonList(sb.toString());
    }

    @Override
    public Integer update(IBaseEntity entity) {
        entity.setUpdateTime(super.getCurrentTime());
      //  logsDao.saveLogs(entity);
        return super.update(entity.getTableName(), entity.getJSONObject(), entity.getJSONObjectId());
    }

    @Override
    public Integer add(IBaseEntity entity) {
        entity.setId(super.generateKey());
        entity.setCreateTime(super.getCurrentTime());
        entity.setUpdateTime(super.getCurrentTime());
       // logsDao.addLogs(entity);
        return super.insert(entity.getTableName(), entity.getJSONObject());
    }

    public List<JSONObject> selectContractSalesList(String condition, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_con_main.ID AS id,\n" +
                "\tt_con_main.CONTRACT_NO AS contractNo,\n" +
                "\tt_con_main.CREATE_USER_ID AS createUserId,\n" +
                "\tt_con_main.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_con_main.HAVE_OVERDUE_NO AS haveOverdueNo,\n" +
                "\tt_con_main.CREATE_TIME AS createTime,\n" +
                "\tt_con_main.CITY AS city,\n" +
                "\tt_con_main.BRAND_NAME AS brandName,\n" +
                "\tt_SYS_COMPANY.NAME AS seller,\n" +
                "\tt_con_main.SELLER_TAX_NO AS sellerTaxNo,\n" +
                "\tt_con_main.SELLER_ADDRESS AS sellerAddress,\n" +
                "\tt_con_main.SELLER_TEL AS sellerTel,\n" +
                "\tt_con_main.BUYER AS buyer,\n" +
                " t_con_main.MANUFACTURER AS manufacturer,\n" +
                " t_con_main.QUALITY_COMPANY AS qualityCompany,\n" +
                " t_con_main.FINANCE_COMPANY AS financeCompany,\n" +
                " t_con_main.BUYER_TAX_NO AS buyerTaxNo,\n" +
                " t_con_main.BUYER_ADDRESS AS buyerAddress,\n" +
                " t_con_main.BUYER_TEL AS buyerTel,\n" +
                " t_con_main.MODEL_NAME AS modelName,\n" +
                " t_con_main.DEVICE_TYPE AS deviceType,\n" +
                " t_con_main.SERIAL_NO AS serialNo,\n" +
                " t_con_main.BUY_TYPE AS buyType,\n" +
                " t_con_main.AUDIT_FLAG AS auditFlag,\n" +
                " t_con_main.CURRENT_AUDITOR_ID AS currentAuditorId,\n" +
                " t_con_main.CURRENT_AUDITOR_NAME AS currentAuditorName,\n" +
                " t_con_main.CAR_FLAG AS carFlag,\n" +
                " t_con_main.SELLER_COMPANY_ID AS sellerCompanyId,\n" +
                " t_con_main.AUTHENTICBRANDS_GROUP AS authenticbrandsGroup,\n" +
                " t_con_main.HAVE_REPAYMENT AS haveRepayment,\n" +
                " t_con_main.TAKE_OVER_MANAGER AS takeOverManager,\n" +
                " t_con_main.HAVE_REPLACEMENT AS haveReplacement,\n" +
                " t_con_main.HAVE_ADVANCES AS haveAdvances,\n" +
                " t_con_main.HAVE_UPLOAD_CONTRACT AS haveUploadContract,\n" +
                " t_con_main.SALES_PRICE AS salesPrice,\n" +
                " t_con_main.CREATE_USER_NAME AS createUserName,\n" +
                " t_con_main.DK_FIRST_MONEY AS dkFirstMoney,\n" +
                " t_con_main.REGION_MANAGER_NAME AS regionManagerName,\n" +
                " t_con_main.REGION_MANAGER_ID AS regionManagerId,\n" +
                " t_con_main.DK_SUM_TOTAL AS dkSumTotal,\n" +
                " t_con_main.DK_SUM_PRINCIPAL AS dkSumPrincipal,\n" +
                " t_con_main.DK_SUM_INTEREST AS dkSumInterest,\n" +
                " t_con_main.FQ_SUM_TOTAL AS fqSumTotal,\n" +
                " t_con_main.FQ_SUM_PRINCIPAL AS fqSumPrincipal,\n" +
                " t_con_main.FQ_SUM_INTEREST AS fqSumInterest,\n" +
                " t_con_main.PROVINCE AS province,\n" +
                " t_con_main.BRAND_ID AS brandId,\n" +
                " t_con_main.MODEL_ID AS modelId,\n" +
                " t_con_main.DK_FIRST_MONEYDX AS dkFirstMoneydx,\n" +
                " t_con_main.HAVE_DELIVERY AS haveDelivery,\n" +
                " t_con_main.DELIVERY_DATE AS deliveryDate,\n" +
                " t_con_main.REGION_DIRECTOR_ID AS regionDirectorId,\n" +
                " t_con_main.REGION_DIRECTOR_NAME AS regionDirectorName,\n" +
                " t_con_main.REGION_ID AS regionId,\n" +
                " t_con_main.REGION_NAME AS regionName,\n" +
                " t_con_main.LEGAL_REPRESENTATIVE_NAME AS legalRepresentativeName,\n" +
                " t_con_main.LEGAL_REPRESENTATIVE_TEL AS legalRepresentativeTel,\n" +
                " t_con_main.ACTUAL_CAR_BUYER AS actualCarBuyer,\n" +
                " t_con_main.ACTUAL_CAR_BUYER_TEL AS actualCarBuyerTel,\n" +
                " t_con_main.DELIVERY_ADDRESS AS deliveryAddress,\n" +
                " t_con_main.BIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                " t_con_main.BIG_REGION_MANAGER_NAME AS bigRegionManagerName,\n" +
                " t_con_main.CONTRACT_TYPE AS contractType,\n" +
                " t_con_main.BUYER_ID AS buyerId,\n" +
                " t_con_main.HAVE_OVERDUE AS haveOverdue,\n" +
                " t_con_main.CREDIT_MANAGER_NAME AS creditManagerName,\n" +
                " t_con_main.CREDIT_MANAGER_ID AS creditManagerId,\n" +
                " t_con_main.DATA_OFFICER_ID AS dataOfficerId,\n" +
                " t_con_main.DATA_OFFICER_NAME AS dataOfficerName,\n" +
                " t_con_main.PAYMENT_AMOUNT AS paymentAmount,\n" +
                " t_con_main.HAVE_SETTLE AS haveSettle,\n" +
                " t_con_main.PAYMENT_TIME AS paymentTime,\n" +
                " t_con_main.DEVICE_STATE AS deviceState,\n" +
                " t_con_main.HAVE_ARRIVAL AS haveArrival,\n" +
                " t_con_main.TOTAL_DOWN_PAYMENT AS totalDownPayment,\n" +
                " t_con_main.TOTAL_ARREARS AS totalArrears,\n" +
                " t_con_main.SITE_POSITION AS sitePosition,\n" +
                " t_con_main.ACTUAL_USER_TEL AS actualUserTel,\n" +
                " t_con_main.ACTUAL_USER AS actualUser,\n" +
                " t_con_main.TOTAL_CUSTOMER_ARREARS AS totalCustomerArrears,\n" +
                " t_con_main.ACCUMULATED_REPAYMENT_AMOUNT AS accumulatedRepaymentAmount,\n" +
                " t_con_main.REGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                " t_con_main.REGIONAL_SUPPORT_NAME AS regionalSupportName,\n" +
                " t_con_main.BUYER_TYPE AS buyerType,\n" +
                " t_con_sales.TOTAL AS total,\n" +
                " t_con_sales.MONTHLY_REPAYMENT AS monthlyRepayment,\n" +
                " t_con_sales.FINANCING_AMOUNT AS financingAmount,\n" +
                " t_con_sales.LOAN_TERM AS loanTerm,\n" +
                " t_con_sales.FIRST_PAYMENT AS firstPayment,\n" +
                " t_con_sales.UNIT_PRICE AS unitPrice,\n" +
                " CASE t_con_main.AUDIT_FLAG\n" +
                "WHEN '0' THEN\n" +
                "\t1\n" +
                "WHEN '-1' THEN\n" +
                "\t2\n" +
                "WHEN '1' THEN\n" +
                "\t3\n" +
                "WHEN '2' THEN\n" +
                "\t4\n" +
                "ELSE\n" +
                "\t5\n" +
                "END AS sortNo\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_SYS_COMPANY ON t_con_main.SELLER_COMPANY_ID = t_SYS_COMPANY.ID\n" +
                "LEFT JOIN t_con_sales ON t_con_main.ID = t_con_sales.MAIN_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_SYS_COMPANY.DEL_FLAG = 0\n" +
                "AND t_con_main.AUDIT_FLAG = 1\n" +
                "AND t_con_sales.LOG_FLAG = 0\n");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition + "\n");
        }
        sql.append("group by t_con_main.id\n" +
                "ORDER BY t_con_main.DELIVERY_DATE DESC,t_con_main.CREATE_TIME desc");
        super.appendPageSql(sql, requestPage, pageSize);
        return super.queryForJsonList(sql.toString());

    }

    public Integer selectContractSalesList(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1)\n" +
                "FROM t_con_main \n" +
                "WHERE 1 = 1\n");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition + "\n");
        }
        String count = super.queryForString(sql.toString());
        return Integer.parseInt(count);
    }
}

